package br.com.vti.sisp_sicrim.antigo.dao;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.UUID;

import br.com.vti.sisp_sicrim.antigo.dao.util.HibernateUtil;

public class SicTabelasDAO {

	private int codigoRetorno = 0; // *ERROR
	private String msgRetorno = ""; // *ERROR
	public long qtdRegistros = 0; // *counter
	public long nroRegistro = 0; // *counter
	private String[] listacol = { "", "GrupoElem", "DescricaoElem", "ChaveAlfaAcesso", "DataAtualizacao", "MatriculaFunc", "CodigoTabela", "CodigoElem", "Observacao", "DataApoio" };
	private ResultSet query = null;

	public String insert(String pGrupoElem, String pDescricaoElem, String pChaveAlfaAcesso, Integer pDataAtualizacao, Integer pMatriculaFunc, Integer pCodigoTabela, Long pCodigoElem, String pObservacao, Integer pDataApoio) {

		String sql = "{ ? = call sp_grava_Sic_Tabelas ( ?,?,?,?,?,?,?,?,? ) }";
		UUID pId;
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {

			CallableStatement stp = HibernateUtil.getConnection().prepareCall(sql);
			stp.registerOutParameter(1, Types.OTHER);
			stp.setString(2, pGrupoElem); // (getColuna("GrupoElem")
			stp.setString(3, pDescricaoElem); // (getColuna("DescricaoElem")
			stp.setString(4, pChaveAlfaAcesso); // (getColuna("ChaveAlfaAcesso")
			stp.setInt(5, pDataAtualizacao); // (getColuna("DataAtualizacao")
			stp.setInt(6, pMatriculaFunc); // (getColuna("MatriculaFunc")
			stp.setInt(7, pCodigoTabela); // (getColuna("CodigoTabela")
			stp.setLong(8, pCodigoElem); // (getColuna("CodigoElem")
			stp.setString(9, pObservacao); // (getColuna("Observacao")
			stp.setInt(10, pDataApoio); // (getColuna("DataApoio")
			// execute SP
			stp.execute();
			pId = (UUID) stp.getObject(1);
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
			return null;
		}
		return String.valueOf(pId);
	}

	public void update(String pId, String pGrupoElem, String pDescricaoElem, String pChaveAlfaAcesso, Integer pDataAtualizacao, Integer pMatriculaFunc, Integer pCodigoTabela, Long pCodigoElem, String pObservacao, Integer pDataApoio) {

		String sql = "{ call sp_atualiza_Sic_Tabelas ( ?,?,?,?,?,?,?,?,?,? ) }";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {

			CallableStatement stp = HibernateUtil.getConnection().prepareCall(sql);
			stp.setObject(1, UUID.fromString(pId)); // getColuna("Id")
			stp.setString(2, pGrupoElem); // getColuna("GrupoElem")
			stp.setString(3, pDescricaoElem); // getColuna("DescricaoElem")
			stp.setString(4, pChaveAlfaAcesso); // getColuna("ChaveAlfaAcesso")
			stp.setInt(5, pDataAtualizacao); // getColuna("DataAtualizacao")
			stp.setInt(6, pMatriculaFunc); // getColuna("MatriculaFunc")
			stp.setInt(7, pCodigoTabela); // getColuna("CodigoTabela")
			stp.setLong(8, pCodigoElem); // getColuna("CodigoElem")
			stp.setString(9, pObservacao); // getColuna("Observacao")
			stp.setInt(10, pDataApoio); // getColuna("DataApoio")
			// execute SP
			stp.execute();
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}
	}

	public void delete(String pId) {

		String sql = "{ call sp_exclui_Sic_Tabelas ( ? ) }";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {

			CallableStatement stp = HibernateUtil.getConnection().prepareCall(sql);
			stp.setObject(1, UUID.fromString(pId)); // getColuna("Id")
			// execute SP
			stp.execute();
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}
	}

	public ResultSet selectCodigoTabela(Integer pCodigoTabela) {

		String sql = "select * from sp_consCodigoTabela_Sic_Tabelas (";
		sql = sql + "pCodigoTabela := '" + String.valueOf(pCodigoTabela) + "' ";
		sql = sql + ")";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {
			query = HibernateUtil.executarQuery(sql);
			if (query == null) {
				return null;
			} else {
				query.first();
				if (query.getRow() == 0) {
					query = null;
					return query;
				}
				if (query.first()) {
					query.last();
					qtdRegistros = query.getRow();
					nroRegistro = 1;
					query.first();
				}
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

		return query;
	}

	public ResultSet selectCodigoTabelaChaveAlfaAcesso(Integer pCodigoTabela, String pChaveAlfaAcesso) {

		String sql = "select * from sp_consCodigoTabelaChaveAlfaAcesso_Sic_Tabelas (";
		sql = sql + "pCodigoTabela := '" + String.valueOf(pCodigoTabela) + "',";
		sql = sql + "pChaveAlfaAcesso := '" + pChaveAlfaAcesso + "' ";
		sql = sql + ")";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {
			query = HibernateUtil.executarQuery(sql);
			if (query == null) {
				return null;
			} else {
				query.first();
				if (query.getRow() == 0) {
					query = null;
					return query;
				}
				if (query.first()) {
					query.last();
					qtdRegistros = query.getRow();
					nroRegistro = 1;
					query.first();
				}
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

		return query;
	}

	public ResultSet selectCodigoTabelaCodigoElem(Integer pCodigoTabela, Long pCodigoElem) {

		String sql = "select * from sp_consCodigoTabelaCodigoElem_Sic_Tabelas (";
		sql = sql + "pCodigoTabela := '" + String.valueOf(pCodigoTabela) + "',";
		sql = sql + "pCodigoElem := '" + String.valueOf(pCodigoElem) + "' ";
		sql = sql + ")";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {
			query = HibernateUtil.executarQuery(sql);
			if (query == null) {
				return null;
			} else {
				query.first();
				if (query.getRow() == 0) {
					query = null;
					return query;
				}
				if (query.first()) {
					query.last();
					qtdRegistros = query.getRow();
					nroRegistro = 1;
					query.first();
				}
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

		return query;
	}

	public ResultSet selectCodigoTabelaCodigoElemChaveAlfaAcesso(Integer pCodigoTabela, Long pCodigoElem, String pChaveAlfaAcesso) {

		String sql = "select * from sp_consCodigoTabelaCodigoElemChaveAlfaAcesso_Sic_Tabelas (";
		sql = sql + "pCodigoTabela := '" + String.valueOf(pCodigoTabela) + "',";
		sql = sql + "pCodigoElem := '" + String.valueOf(pCodigoElem) + "',";
		sql = sql + "pChaveAlfaAcesso := '" + pChaveAlfaAcesso + "' ";
		sql = sql + ")";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {
			query = HibernateUtil.executarQuery(sql);
			if (query == null) {
				return null;
			} else {
				query.first();
				if (query.getRow() == 0) {
					query = null;
					return query;
				}
				if (query.first()) {
					query.last();
					qtdRegistros = query.getRow();
					nroRegistro = 1;
					query.first();
				}
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

		return query;
	}

	public ResultSet selectTabcha(Integer pCodigoTabela, String pChaveAlfaAcesso) {

		String sql = "select * from sp_consTabcha_Sic_Tabelas (";
		sql = sql + "pCodigoTabela := '" + String.valueOf(pCodigoTabela) + "',";
		sql = sql + "pChaveAlfaAcesso := '" + pChaveAlfaAcesso + "' ";
		sql = sql + ")";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {
			query = HibernateUtil.executarQuery(sql);
			if (query == null) {
				return null;
			} else {
				query.first();
				if (query.getRow() == 0) {
					query = null;
					return query;
				}
				if (query.first()) {
					query.last();
					qtdRegistros = query.getRow();
					nroRegistro = 1;
					query.first();
				}
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

		return query;
	}

	public ResultSet selectTabele(Integer pCodigoTabela, Long pCodigoElem) {

		String sql = "select * from sp_consTabele_Sic_Tabelas (";
		sql = sql + "pCodigoTabela := '" + String.valueOf(pCodigoTabela) + "',";
		sql = sql + "pCodigoElem := '" + String.valueOf(pCodigoElem) + "' ";
		sql = sql + ")";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {
			query = HibernateUtil.executarQuery(sql);
			if (query == null) {
				return null;
			} else {
				query.first();
				if (query.getRow() == 0) {
					query = null;
					return query;
				}
				if (query.first()) {
					query.last();
					qtdRegistros = query.getRow();
					nroRegistro = 1;
					query.first();
				}
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

		return query;
	}

	public ResultSet readTabchage(String pChaveAlfaAcesso, Integer pCodigoTabela) {

		String sql = "select * from sp_leTabchaMaiorig_Sic_Tabelas (";
		sql = sql + "pCodigoTabela := '" + String.valueOf(pCodigoTabela) + "',";
		sql = sql + "pChaveAlfaAcesso := '" + pChaveAlfaAcesso + "' ";
		sql = sql + ")";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {
			query = HibernateUtil.executarQuery(sql);
			if (query == null) {
				return null;
			} else {
				query.first();
				if (query.getRow() == 0) {
					query = null;
					return query;
				}
				if (query.first()) {
					query.last();
					qtdRegistros = query.getRow();
					nroRegistro = 1;
					query.first();
				}
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

		return query;
	}

	public ResultSet readTabdecge(Integer pCodigoTabela, String pDescricaoElem) {

		String sql = "select * from sp_leTabdecMaiorig_Sic_Tabelas (";
		sql = sql + "pCodigoTabela := '" + String.valueOf(pCodigoTabela) + "',";
		sql = sql + "pDescricaoElem := '" + pDescricaoElem + "' ";
		sql = sql + ")";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {
			query = HibernateUtil.executarQuery(sql);
			if (query == null) {
				return null;
			} else {
				query.first();
				if (query.getRow() == 0) {
					query = null;
					return query;
				}
				if (query.first()) {
					query.last();
					qtdRegistros = query.getRow();
					nroRegistro = 1;
					query.first();
				}
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

		return query;
	}

	public ResultSet readTabelege(Integer pCodigoTabela, Long pCodigoElem) {

		String sql = "select * from sp_leTabeleMaiorig_Sic_Tabelas (";
		sql = sql + "pCodigoTabela := '" + String.valueOf(pCodigoTabela) + "',";
		sql = sql + "pCodigoElem := '" + String.valueOf(pCodigoElem) + "' ";
		sql = sql + ")";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {
			query = HibernateUtil.executarQuery(sql);
			if (query == null) {
				return null;
			} else {
				query.first();
				if (query.getRow() == 0) {
					query = null;
					return query;
				}
				if (query.first()) {
					query.last();
					qtdRegistros = query.getRow();
					nroRegistro = 1;
					query.first();
				}
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

		return query;
	}

	public ResultSet findCodigoTabela(Integer pCodigoTabela) {

		String sql = "select * from sp_pesqCodigoTabela_Sic_Tabelas (";
		sql = sql + "pCodigoTabela := '" + String.valueOf(pCodigoTabela) + "' ";
		sql = sql + ")";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {
			query = HibernateUtil.executarQuery(sql);
			if (query == null) {
				return null;
			} else {
				query.first();
				if (query.getRow() == 0) {
					query = null;
					return query;
				}
				if (query.first()) {
					query.last();
					qtdRegistros = query.getRow();
					nroRegistro = 1;
					query.first();
				}
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

		return query;
	}

	public ResultSet findCodigoTabelaasc(Integer pCodigoTabela) {

		String sql = "select * from sp_pesqCodigoTabelaClas_Sic_Tabelas (";
		sql = sql + "pCodigoTabela := '" + String.valueOf(pCodigoTabela) + "' ";
		sql = sql + ")";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {
			query = HibernateUtil.executarQuery(sql);
			if (query == null) {
				return null;
			} else {
				query.first();
				if (query.getRow() == 0) {
					query = null;
					return query;
				}
				if (query.first()) {
					query.last();
					qtdRegistros = query.getRow();
					nroRegistro = 1;
					query.first();
				}
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

		return query;
	}

	public ResultSet findid(String id) {

		String sql = "select * from sp_pesqPorId_Sic_Tabelas (";
		sql = sql + "pId := '" + id + "' ";
		sql = sql + ")";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {
			query = HibernateUtil.executarQuery(sql);
			if (query == null) {
				return null;
			} else {
				query.first();
				if (query.getRow() == 0) {
					query = null;
					return query;
				}
				if (query.first()) {
					query.last();
					qtdRegistros = query.getRow();
					nroRegistro = 1;
					query.first();
				}
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

		return query;
	}

	public ResultSet findTabcha(Integer pCodigoTabela, String pChaveAlfaAcesso) {

		String sql = "select * from sp_pesqTabcha_Sic_Tabelas (";
		sql = sql + "pCodigoTabela := '" + String.valueOf(pCodigoTabela) + "',";
		sql = sql + "pChaveAlfaAcesso := '" + pChaveAlfaAcesso + "' ";
		sql = sql + ")";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {
			query = HibernateUtil.executarQuery(sql);
			if (query == null) {
				return null;
			} else {
				query.first();
				if (query.getRow() == 0) {
					query = null;
					return query;
				}
				if (query.first()) {
					query.last();
					qtdRegistros = query.getRow();
					nroRegistro = 1;
					query.first();
				}
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

		return query;
	}

	public ResultSet findTabele(Integer pCodigoTabela, Long pCodigoElem) {

		String sql = "select * from sp_pesqTabele_Sic_Tabelas (";
		sql = sql + "pCodigoTabela := '" + String.valueOf(pCodigoTabela) + "',";
		sql = sql + "pCodigoElem := '" + String.valueOf(pCodigoElem) + "' ";
		sql = sql + ")";
		codigoRetorno = 0;
		qtdRegistros = 0;
		nroRegistro = 0;
		msgRetorno = "";
		try {
			query = HibernateUtil.executarQuery(sql);
			if (query == null) {
				return null;
			} else {
				query.first();
				if (query.getRow() == 0) {
					query = null;
					return query;
				}
				if (query.first()) {
					query.last();
					qtdRegistros = query.getRow();
					nroRegistro = 1;
					query.first();
				}
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
		}

		return query;
	}

	public ResultSet pesquisaProximo() {
		// query
		try {
			if (query == null)
				return null;
			if (query.getRow() == 0) {
				query = null;
				return null;
			}
			query.next();
			if (query.getRow() == 0) {
				query = null;
				return null;
			}
			if (query != null) {
				nroRegistro++;
			}
			return query;
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
			return null;
		}
	}

	public boolean temProximo() {
		try {
			if (query == null)
				return false;
			if (query.getRow() == 0) {
				return false;
			}
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
			return false;
		}
		return (nroRegistro < qtdRegistros);
	}

	public void fecha() {
		if (query == null)
			return;
		try {
			query.close();
			query = null;
		} catch (SQLException e) {
			codigoRetorno = 999;
			msgRetorno = e.getMessage();
			query = null;
		}
	}

	public int getColuna(String coluna) {
		for (int i = 0; i < listacol.length; i++) {
			if (listacol[i].equals(coluna))
				return i;
		}
		return -1;
	}

}
